/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MySqlAccess;

import com.mysql.jdbc.TimeUtil;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

/**
 *
 * @author LNThanh
 */
public class UserRating 
{
    public List<RatingInfo> GetBy_User_Date(int userID, Timestamp begin, Timestamp end)
    {
        String query = "SELECT RATING_USERID, RATING_POSTID, RATING_RATING, RATING_TIMESTAMP" +
                " FROM WP_RATINGS WHERE RATING_TIMESTAMP >= '" + begin.getTime()/1000 +
                "' AND RATING_TIMESTAMP < '" + end.getTime()/1000 + "' AND RATING_USERID=" + userID;
        return ExcuteQuery(query);
    }

    public List<RatingInfo> GetByDate(Timestamp begin, Timestamp end)
    {
        String query = "SELECT RATING_USERID, RATING_POSTID, RATING_RATING, RATING_TIMESTAMP" +
                " FROM WP_RATINGS WHERE RATING_TIMESTAMP >= '" + begin.getTime()/1000 +
                "' AND RATING_TIMESTAMP < '" + end.getTime()/1000 + "'";
        return ExcuteQuery(query);
    }

    public List<RatingInfo> GetItemByUserID(int id)
    {
        String query = "SELECT RATING_USERID, RATING_POSTID, RATING_RATING, RATING_TIMESTAMP"
                + " FROM WP_RATINGS WHERE RATING_USERID = " + id;
        return ExcuteQuery(query);
    }    

    public List<RatingInfo> GetItemByPostID(int id)
    {
        String query = "SELECT RATING_USERID, RATING_POSTID, RATING_RATING, RATING_TIMESTAMP"
                + " FROM WP_RATINGS WHERE RATING_POSTID = " + id;
        return ExcuteQuery(query);
    }

    public List<RatingInfo> GetAllItem()
    {
        String query = "SELECT RATING_USERID, RATING_POSTID, RATING_RATING, RATING_TIMESTAMP FROM WP_RATINGS";
        return ExcuteQuery(query);
    }
    
    public List<RatingInfo> ExcuteQuery(String query)
    {
        List<RatingInfo> result = new ArrayList<RatingInfo>();
        
        try
        {            
            DataAcessHelper da = DataAcessHelper.GetInstance();
            ResultSet rs =  da.ExcuteQuery(query);
            
            int idxUserID = rs.findColumn("rating_userid");
            int idxPostingID = rs.findColumn("rating_postid");
            int idxRatingScore = rs.findColumn("rating_rating");
            int idxTime = rs.findColumn("rating_timestamp");            
            
            rs.beforeFirst();
            
            while(rs.next())
            {
                RatingInfo ratingInfo = new RatingInfo();
                ratingInfo = new RatingInfo();
                ratingInfo.m_dateTime =new Date(Long.parseLong(rs.getString(idxTime))*1000);
                ratingInfo.m_idItem = rs.getInt(idxPostingID);
                ratingInfo.m_idUser = rs.getInt(idxUserID);
                ratingInfo.m_rating = rs.getInt(idxRatingScore);
               
                result.add(ratingInfo);
            }
            rs.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        
        return result;
    }
    
    public static void main(String[] argvs)
    {
        UserRating userRating = new UserRating();        
        List<RatingInfo> lstRatingInfo = userRating.GetAllItem();
        System.out.println("Number of row:" + lstRatingInfo.size());
       
        for (RatingInfo ratingInfo : lstRatingInfo)
        {
            System.out.println("----------------------------------------------------");
            System.out.println("usr: " + ratingInfo.m_idUser + " item: " + ratingInfo.m_idItem + " datatime: " + ratingInfo.m_dateTime
                    + " score: " + ratingInfo.m_rating);
        }
    }
}
